package com.gitee.tanmingjian.httptunnel.core;

import com.gitee.tanmingjian.httptunnel.core.bean.*;
import com.gitee.tanmingjian.httptunnel.core.exception.ConnectionException;
import com.gitee.tanmingjian.httptunnel.core.util.IOUtils;
import com.gitee.tanmingjian.httptunnel.core.util.MySQLUtils;
import com.gitee.tanmingjian.httptunnel.core.util.StringUtils;
import com.gitee.tanmingjian.httptunnel.core.util.TunnelProtocolUtils;
import com.github.benmanes.caffeine.cache.Cache;
import com.github.benmanes.caffeine.cache.Caffeine;
import com.mysql.cj.MysqlType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

public class HttpTunnelService {

    private static final Logger log = LoggerFactory.getLogger(HttpTunnelService.class);
    public static final List<MysqlType> MYSQL_TEXT_TYPE_LIST = Arrays.asList(MysqlType.LONGTEXT, MysqlType.TEXT, MysqlType.MEDIUMTEXT, MysqlType.TINYTEXT);

    private final Cache<String, Connection> cache2 = Caffeine.newBuilder()
            .maximumSize(100)
            .expireAfterAccess(30, TimeUnit.MINUTES)
            .evictionListener((key, value, cause) -> {
                log.info("自动关闭连接:" + key);
                // 清除缓存时关闭数据库连接
                if (value != null) {
                    try {
                        ((Connection) value).close();
                    } catch (SQLException e) {
                        log.error("关闭数据库连接失败", e);
                    }
                }
            })
            .build();

    public String getIndexHtml(String basePath) throws IOException {
        try(InputStream inputStream = this.getClass().getResourceAsStream("/index.html")) {
            if (inputStream == null) {
                return "缺少模板文件";
            }
            String s = IOUtils.toString(inputStream, StandardCharsets.UTF_8.name());
            return StringUtils.replace(s, "${contextPath}", basePath + "/");
        }
    }

    public byte[] process(QueryParam queryParam, String sessionId) {
        String sessionFlag = queryParam.getSessionFlag(sessionId);
        String actn = queryParam.getActn();
        if ("V".equals(actn)) {
            ConnectionInfo info = MySQLUtils.getConnectionInfo(queryParam.getHost(), queryParam.getPort());
            return TunnelProtocolUtils.buildConnInfoByte(info);
        } else if ("C".equals(actn) || "Q".equals(actn)) {
            try {
                String url = MessageFormat.format("jdbc:mysql://{0}:{1}/{2}", queryParam.getHost(), queryParam.getPort(), queryParam.getDb());
                Connection conn = cache2.get(sessionFlag, key -> {
                    try {
                        return DriverManager.getConnection(url, queryParam.getLogin(), queryParam.getPassword());
                    } catch (SQLException e) {
                        throw new ConnectionException(e);
                    }
                });

                if (conn == null) {
                    throw new ConnectionException();
                }

                if ("C".equals(actn)) {
                    return TunnelProtocolUtils.buildConnInfoByte(new ConnectionInfo(queryParam.getHost(), "10", conn.getMetaData().getDatabaseProductVersion()));
                } else {
                    log.debug("查询SQL：{}", String.join(";", queryParam.getQ()));
                    return TunnelProtocolUtils.buildQueryResultsByte(getQueryResults(conn, queryParam.getQ()));
                }
            } catch (ConnectionException e) {
                log.debug("数据库连接失败", e);
                return TunnelProtocolUtils.buildErrorByte(2002, "由于目标计算机积极拒绝，无法连接。");
            } catch (Exception e) {
                log.debug("出现异常", e);
                return TunnelProtocolUtils.buildErrorByte(500, e.getMessage());
            }
        } else {
            log.error("不支持的 actn 参数：{}", actn);
            return TunnelProtocolUtils.buildErrorByte(202, "invalid parameters");
        }
    }

    private List<QueryResult> getQueryResults(Connection conn, List<String> q) {
        List<QueryResult> list = new ArrayList<>();
        if (q != null && !q.isEmpty()) {
            for (int i = 0; i < q.size(); i++) {
                String sql = q.get(i);
                if (sql == null || StringUtils.EMPTY.equals(sql)) {
                    continue;
                }
                QueryResult queryResult = new QueryResult();
                list.add(queryResult);
                try(Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
                    boolean hasResult = stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
                    int affectRows = stmt.getUpdateCount();
                    if (affectRows < 0) {
                        affectRows = 0;
                    }
                    if (hasResult) {
                        try(ResultSet rs1 = stmt.getGeneratedKeys(); ResultSet rs = stmt.getResultSet()) {
                            int insertId = 0;
                            if (rs1.next()) {
                                insertId = rs1.getInt(1);
                            }

                            com.mysql.cj.jdbc.result.ResultSetMetaData metaData = (com.mysql.cj.jdbc.result.ResultSetMetaData) rs.getMetaData();
                            int numFields = metaData.getColumnCount();

                            rs.last();
                            int numRows = rs.getRow();
                            rs.beforeFirst();

                            ResultSetHeader resultSetHeader = new ResultSetHeader(0, affectRows, insertId, numFields, numRows);
                            ResultSetData resultSetData = new ResultSetData(Arrays.stream(metaData.getFields()).map(field -> {
                                FieldHeader fieldHeader = new FieldHeader();
                                fieldHeader.setName(field.getName());
                                fieldHeader.setTableName(field.getTableName());
                                // LONGTEXT, TEXT, MEDIUMTEXT, TINYTEXT 类型的字段在 Navicat 上会显示为 (BLOB) xx bytes,
                                // 为了直接显示文本，故将类型更改为 FIELD_TYPE_VAR_STRING
                                if (MYSQL_TEXT_TYPE_LIST.contains(field.getMysqlType())) {
                                    fieldHeader.setTypeId(MysqlType.FIELD_TYPE_VAR_STRING);
                                } else {
                                    fieldHeader.setTypeId(field.getMysqlTypeId());
                                }
                                fieldHeader.setFlags((int) field.getFlags());
                                fieldHeader.setLength((int) field.getLength());
                                return fieldHeader;
                            }).collect(Collectors.toList()), resultSet2DataList(rs, numFields));

                            queryResult.setResultSetHeader(resultSetHeader);
                            queryResult.setResultSetBody(resultSetData);
                        }
                    } else {
                        ResultSetHeader resultSetHeader = new ResultSetHeader(0, affectRows, 0, 0, 0);
                        queryResult.setResultSetHeader(resultSetHeader);
                        queryResult.setResultSetBody(StringUtils.EMPTY);
                    }
                } catch (SQLException e) {
                    ResultSetHeader resultSetHeader = new ResultSetHeader(e.getErrorCode(), 0, 0, 0, 0);
                    queryResult.setResultSetHeader(resultSetHeader);
                    queryResult.setResultSetBody(e.getMessage());
                }

                queryResult.setHasNext(i < q.size() - 1);
            }
        }
        return list;
    }

    private List<List<String>> resultSet2DataList(ResultSet rs, int numFields) throws SQLException {
        List<List<String>> data = new ArrayList<>();
        while (rs.next()) {
            List<String> row = new ArrayList<>(numFields);
            for (int i = 1; i <= numFields; i++) {
                row.add(rs.getString(i));
            }
            data.add(row);
        }
        return data;
    }
}
